package com.lazyfish.scss.dao;

import com.lazyfish.scss.bean.Course;
import com.lazyfish.scss.util.DBUtil;
import com.lazyfish.scss.util.ResultSetToList;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDAO {
    public List<Course> getAllCourse() {
        String sql="SELECT * FROM course";
        List<Course> list = new ArrayList<Course>();
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            try {
                list = ResultSetToList.resultSetToList(rs, Course.class);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return list;
    }
    public List<Course> getAllSelectCourses(int u_id) {
        String sql="SELECT course.* FROM course JOIN elective on course.course_id=elective.course_id WHERE elective.user_id=?";
        List<Course> list = new ArrayList<Course>();
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1,u_id);
            ResultSet rs = ps.executeQuery();
            try {
                list = ResultSetToList.resultSetToList(rs, Course.class);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return list;
    }
    public int selectCourses(int u_id,int c_id) {
        String sql="INSERT INTO `elective` (`user_id`, `course_id`) VALUES (?, ?)";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1,u_id);
            ps.setInt(2,c_id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
        return 1;
    }
    public int addCourse(String course_name,String teacher,String point,String timeend,String location,String limited,String timeon) {
        String sql="INSERT INTO `course` (`course_name`, `teacher`, `point`, `timeend`, `location`, `limited`, `timeon`) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1,course_name);
            ps.setString(2,teacher);
            ps.setString(3,point);
            ps.setString(4,timeend);
            ps.setString(5,limited);
            ps.setString(6,location);
            ps.setString(7,timeon);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
        return 1;
    }
}
